package com.example.demo.web.dao;

import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.Entity.UserLogEntity;
import com.example.demo.web.Entity.UserOperLogEntity;
import com.example.demo.web.util.XinLongUtil;


@Repository
public class LogDAO {
	private Logger log = LogManager.getLogger(LogDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
    
    
    public int insertLog(UserOperLogEntity logEntity){    	
    	String sql = "INSERT INTO tbl_user_operlog (id,Createtime, Descriptor, Name, RemoteAddr,Username,User_checkname,User_create,Org_id)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?)";
    	try{
    		return jdbcTemplate.update(sql,logEntity.getId(),logEntity.getCreatetime(), logEntity.getDescriptor(),logEntity.getRname(),logEntity.getRemoteaddr(),
    				logEntity.getUsername(), logEntity.getUser_check(),logEntity.getUser_create(),logEntity.getOrg_id());
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}    	
    }
    
    public List<UserOperLogEntity> findAll(int pageNumber,int pageSize,String start,String end){
    	String sql = "SELECT log.Createtime createtime,log.Descriptor descriptor,log.Name name,log.RemoteAddr remoteaddr,log.Username,log.User_checkname user_check, log.User_create,org.Orgcode orgname FROM tbl_user_operlog log left join tbl_organization org on log.Org_id = org.id where log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' ORDER BY log.Createtime LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserOperLogEntity.class));
    }
    
    
    
    /*==========================================tbl_user_operlog===================================================*/
    public List<UserOperLogEntity> findUserManagerAll(int pageNumber,int pageSize,String start,String end,String val){
    	String sql = "";
    	if(val.equals("")){
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,us.Username username, ur.Username operator,uc.Username checkname,org.Name orgname FROM tbl_user_operlog log left join tbl_users us on log.User_id = us.id left join tbl_users ur on log.User_create_id = ur.id left join tbl_users uc on log.User_check_id = uc.id left join tbl_organization org on us.Org_id = org.id where log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' ORDER BY log.Createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	}else{
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,us.Username username, ur.Username operator,uc.Username checkname,org.Name orgname FROM tbl_user_operlog log left join tbl_users us on log.User_id = us.id left join tbl_users ur on log.User_create_id = ur.id left join tbl_users uc on log.User_check_id = uc.id left join tbl_organization org on us.Org_id = org.id where log.Name = '" + val + "' and log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' ORDER BY log.Createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	}
    	
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserOperLogEntity.class));
    }
    
    public List<UserOperLogEntity> findUserManagerSubAll(int pageNumber,int pageSize,String start,String end,String val,List<OrganizationEntity> orgs){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or log.org_id = '" + org.getId() + "'");
    		}
    		i++;
    	}
    	if(val.equals("")){
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,log.Username username, log.User_create operator,log.User_checkname checkname,org.Orgcode orgname FROM tbl_user_operlog log left join tbl_organization org on log.Org_id = org.id where log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' and (log.Org_id = '" + sb.toString() + ") ORDER BY log.Createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	}else{
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,log.Username username, log.User_create operator,log.User_checkname checkname,org.Orgcode orgname FROM tbl_user_operlog log left join tbl_organization org on log.Org_id = org.id where log.Name = '" + val + "' and log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' and (log.Org_id = '" + sb.toString() + ") ORDER BY log.Createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	}
    	
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserOperLogEntity.class));
    }
    
    public int getumlogCount(String start,String end,String val){
    	String sql = "";
    	if(val.equals("")){
    		sql = "SELECT count(*) from tbl_user_operlog where Createtime >= '" + start + "' and Createtime <= '" + end + "'";
    	}else{
    		sql = "SELECT count(*) from tbl_user_operlog where log.Name = '" + val + "' and log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<UserOperLogEntity> findUserManagerSubAll(String start,String end,String val,List<OrganizationEntity> orgs){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or log.org_id = '" + org.getId() + "'");
    		}
    		i++;
    	}
    	if(val.equals("")){
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,log.Username username, log.User_create operator,log.User_checkname checkname,org.Orgcode orgname FROM tbl_user_operlog log left join tbl_organization org on log.Org_id = org.id where log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' and (log.Org_id = '" + sb.toString() + ") ORDER BY log.Createtime DESC"; 
    	}else{
    		sql = "SELECT log.Name rname,log.Descriptor descriptor,log.Createtime createtime,log.Username username, log.User_create operator,log.User_checkname checkname,org.Orgcode orgname FROM tbl_user_operlog log left join tbl_organization org on log.Org_id = org.id where log.Name = '" + val + "' and log.Createtime >= '" + start + "' and log.Createtime <= '" + end + "' and (log.Org_id = '" + sb.toString() + ") ORDER BY log.Createtime DESC"; 
    	}
    	
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserOperLogEntity.class));
    }
    
    public int getumlogSubCount(String start,String end,String val,List<OrganizationEntity> orgs){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or Org_id = '" + org.getId() + "'");
    		}
    		i++;
    	}
    	if(val.equals("")){
    		sql = "SELECT count(*) from tbl_user_operlog where Createtime >= '" + start + "' and Createtime <= '" + end + "' and (Org_id = '" + sb.toString() + ")";
    	}else{
    		sql = "SELECT count(*) from tbl_user_operlog where Name = '" + val + "' and Createtime >= '" + start + "' and Createtime <= '" + end + "' and (Org_id = '" + sb.toString() + ")";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    /*==========================================tbl_user_log===================================================*/
    public int insertUserLog(UserLogEntity logEntity){    	
    	String sql = "INSERT INTO tbl_usersyslog (id,args_json, create_check_userid, createtime, createusername,descriptor,rname,proceed,remote_addr,orgid,method,userid)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?)";
    	try{
    		return jdbcTemplate.update(sql,logEntity.getId(),logEntity.getArgs_json(), logEntity.getCreate_check_userid(),logEntity.getCreatetime(),logEntity.getCreateusername(),
    				logEntity.getDescriptor(), logEntity.getRname(),logEntity.getProceed(),logEntity.getRemote_addr(),logEntity.getOrgid(),logEntity.getMethod(),logEntity.getUserid());
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}    	
    }
    
    /*===================================tbl_organization===============================================*/
    public List<OrganizationEntity> findSubOrg(String parentid) throws EmptyResultDataAccessException{
    	String sql = "SELECT DATA.* FROM(SELECT @ids as _ids,( SELECT @ids := GROUP_CONCAT(id) FROM tbl_organization WHERE FIND_IN_SET(Parentid, @ids)) as cids," + 
			"@l := @l+1 as level FROM tbl_organization, (SELECT @ids :='" + parentid + "', @l := 0 ) b WHERE @ids IS NOT NULL" + 
			") id, tbl_organization DATA WHERE FIND_IN_SET(DATA.id, ID._ids)";
    	try{
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    	}catch(Exception e){
    		return null;
    	}
    	
    }
    
    /*==========================================tbl_usersyslog================================================================*/
    public int insertsysLog(UserLogEntity logEntity){    	
    	String sql = "INSERT INTO tbl_usersyslog (id,args_json, create_check_userid, createtime, createusername,descriptor,rname,proceed,remote_addr,orgid,method,userid)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?)";
    	try{
    		return jdbcTemplate.update(sql,logEntity.getId(),logEntity.getArgs_json(), logEntity.getCreate_check_userid(),logEntity.getCreatetime(),logEntity.getCreateusername(),
    				logEntity.getDescriptor(), logEntity.getRname(),logEntity.getProceed(),logEntity.getRemote_addr(),logEntity.getOrgid(),logEntity.getMethod(),logEntity.getUserid());
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}    	
    }
    
    public List<UserLogEntity> findUserSysSubAll(int pageNumber,int pageSize,String start,String end,String sproperty,String val,List<OrganizationEntity> orgs,String orgid){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	if(orgid.equalsIgnoreCase("")){
    		int i = 0;
        	for(OrganizationEntity org : orgs){
        		if(i == 0){
        			sb.append(org.getId() + "'");
        		}else{
        			sb.append(" or log.orgid = '" + org.getId() + "'");
        		}
        		i++;
        	}
        	if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.remote_addr = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.createusername = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.rname = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}
    	}else{
    		if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.remote_addr = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.createusername = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.rname = '" + val + "' ORDER BY log.createtime DESC LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	    	}
    		}
    	}    	
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserLogEntity.class));
    }
    
    public int getsyslogSubCount(String start,String end,String sproperty,String val,List<OrganizationEntity> orgs,String orgid){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	if(orgid.equalsIgnoreCase("")){
    		int i = 0;
        	for(OrganizationEntity org : orgs){
        		if(i == 0){
        			sb.append(org.getId() + "'");
        		}else{
        			sb.append(" or log.orgid = '" + org.getId() + "'");
        		}
        		i++;
        	}
        	if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ")"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.remote_addr = '" + val + "'"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ")"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.createusername = '" + val + "'"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ")"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.rname = '" + val + "'"; 
    	    	}
    		}
    	}else{
    		if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "'"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.remote_addr = '" + val + "'"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "'"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.createusername = '" + val + "'"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "'"; 
    	    	}else{
    	    		sql = "SELECT count(*) FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + " and log.rname = '" + val + "'"; 
    	    	}
    		}
    	}    	
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<UserLogEntity> findUserSysSubAll(String start,String end,String sproperty,String val,List<OrganizationEntity> orgs,String orgid){
    	String sql = "";
    	StringBuilder sb = new StringBuilder();
    	if(orgid.equalsIgnoreCase("")){
    		int i = 0;
        	for(OrganizationEntity org : orgs){
        		if(i == 0){
        			sb.append(org.getId() + "'");
        		}else{
        			sb.append(" or log.orgid = '" + org.getId() + "'");
        		}
        		i++;
        	}
        	if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.remote_addr = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.createusername = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and (log.orgid = '" + sb.toString() + ") and log.rname = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}
    	}else{
    		if(sproperty.equalsIgnoreCase("ipaddr")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.remote_addr = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("username")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.createusername = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}else if(sproperty.equalsIgnoreCase("rname")){
    			if(val.equals("")){
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' ORDER BY log.createtime DESC"; 
    	    	}else{
    	    		sql = "SELECT log.*,org.Name orgname FROM tbl_usersyslog log left join tbl_organization org on log.orgid = org.id where log.createtime >= '" + start + "' and log.createtime <= '" + end + "' and log.orgid = '" + orgid + "' and log.rname = '" + val + "' ORDER BY log.createtime DESC"; 
    	    	}
    		}
    	}    	
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserLogEntity.class));
    }
    
    
}
